{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 12.1 Reading from and Writing to JSON Files"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 12.1.1 Loading a JSON File into a DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>prizes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>{'year': '2019', 'category': 'chemistry', 'lau...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>{'year': '2019', 'category': 'economics', 'lau...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>{'year': '2019', 'category': 'literature', 'la...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>{'year': '2019', 'category': 'peace', 'laureat...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>{'year': '2019', 'category': 'physics', 'overa...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                              prizes\n",
       "0  {'year': '2019', 'category': 'chemistry', 'lau...\n",
       "1  {'year': '2019', 'category': 'economics', 'lau...\n",
       "2  {'year': '2019', 'category': 'literature', 'la...\n",
       "3  {'year': '2019', 'category': 'peace', 'laureat...\n",
       "4  {'year': '2019', 'category': 'physics', 'overa..."
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nobel = pd.read_json(\"nobel.json\")\n",
    "nobel.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'year': '2019',\n",
       " 'category': 'literature',\n",
       " 'laureates': [{'id': '980',\n",
       "   'firstname': 'Peter',\n",
       "   'surname': 'Handke',\n",
       "   'motivation': '\"for an influential work that with linguistic ingenuity has explored the periphery and the specificity of human experience\"',\n",
       "   'share': '1'}]}"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nobel.loc[2, \"prizes\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(nobel.loc[2, \"prizes\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'year': '2019',\n",
       " 'category': 'chemistry',\n",
       " 'laureates': [{'id': '976',\n",
       "   'firstname': 'John',\n",
       "   'surname': 'Goodenough',\n",
       "   'motivation': '\"for the development of lithium-ion batteries\"',\n",
       "   'share': '3'},\n",
       "  {'id': '977',\n",
       "   'firstname': 'M. Stanley',\n",
       "   'surname': 'Whittingham',\n",
       "   'motivation': '\"for the development of lithium-ion batteries\"',\n",
       "   'share': '3'},\n",
       "  {'id': '978',\n",
       "   'firstname': 'Akira',\n",
       "   'surname': 'Yoshino',\n",
       "   'motivation': '\"for the development of lithium-ion batteries\"',\n",
       "   'share': '3'}]}"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chemistry_2019 = nobel.loc[0, \"prizes\"]\n",
    "chemistry_2019"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>category</th>\n",
       "      <th>laureates</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019</td>\n",
       "      <td>chemistry</td>\n",
       "      <td>[{'id': '976', 'firstname': 'John', 'surname':...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year   category                                          laureates\n",
       "0  2019  chemistry  [{'id': '976', 'firstname': 'John', 'surname':..."
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.json_normalize(data = chemistry_2019)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>firstname</th>\n",
       "      <th>surname</th>\n",
       "      <th>motivation</th>\n",
       "      <th>share</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>976</td>\n",
       "      <td>John</td>\n",
       "      <td>Goodenough</td>\n",
       "      <td>\"for the development of lithium-ion batteries\"</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>977</td>\n",
       "      <td>M. Stanley</td>\n",
       "      <td>Whittingham</td>\n",
       "      <td>\"for the development of lithium-ion batteries\"</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>978</td>\n",
       "      <td>Akira</td>\n",
       "      <td>Yoshino</td>\n",
       "      <td>\"for the development of lithium-ion batteries\"</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    id   firstname      surname  \\\n",
       "0  976        John   Goodenough   \n",
       "1  977  M. Stanley  Whittingham   \n",
       "2  978       Akira      Yoshino   \n",
       "\n",
       "                                       motivation share  \n",
       "0  \"for the development of lithium-ion batteries\"     3  \n",
       "1  \"for the development of lithium-ion batteries\"     3  \n",
       "2  \"for the development of lithium-ion batteries\"     3  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.json_normalize(data = chemistry_2019, record_path = \"laureates\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>firstname</th>\n",
       "      <th>surname</th>\n",
       "      <th>motivation</th>\n",
       "      <th>share</th>\n",
       "      <th>year</th>\n",
       "      <th>category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>976</td>\n",
       "      <td>John</td>\n",
       "      <td>Goodenough</td>\n",
       "      <td>\"for the development of lithium-ion batteries\"</td>\n",
       "      <td>3</td>\n",
       "      <td>2019</td>\n",
       "      <td>chemistry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>977</td>\n",
       "      <td>M. Stanley</td>\n",
       "      <td>Whittingham</td>\n",
       "      <td>\"for the development of lithium-ion batteries\"</td>\n",
       "      <td>3</td>\n",
       "      <td>2019</td>\n",
       "      <td>chemistry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>978</td>\n",
       "      <td>Akira</td>\n",
       "      <td>Yoshino</td>\n",
       "      <td>\"for the development of lithium-ion batteries\"</td>\n",
       "      <td>3</td>\n",
       "      <td>2019</td>\n",
       "      <td>chemistry</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    id   firstname      surname  \\\n",
       "0  976        John   Goodenough   \n",
       "1  977  M. Stanley  Whittingham   \n",
       "2  978       Akira      Yoshino   \n",
       "\n",
       "                                       motivation share  year   category  \n",
       "0  \"for the development of lithium-ion batteries\"     3  2019  chemistry  \n",
       "1  \"for the development of lithium-ion batteries\"     3  2019  chemistry  \n",
       "2  \"for the development of lithium-ion batteries\"     3  2019  chemistry  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.json_normalize(\n",
    "    data = chemistry_2019,\n",
    "    record_path = \"laureates\",\n",
    "    meta = [\"year\", \"category\"]\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**NOTE**: I've commented out the code below so that the Notebook can run without raising an error."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# pd.json_normalize(\n",
    "#     data = nobel[\"prizes\"],\n",
    "#     record_path = \"laureates\",\n",
    "#     meta = [\"year\", \"category\"]\n",
    "# )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "cheese_consumption = {\n",
    "    \"France\": 57.9,\n",
    "    \"Germany\": 53.2,\n",
    "    \"Luxembourg\": 53.2\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "57.9"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cheese_consumption.setdefault(\"France\", 100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "57.9"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cheese_consumption[\"France\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "48"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cheese_consumption.setdefault(\"Italy\", 48)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'France': 57.9, 'Germany': 53.2, 'Luxembourg': 53.2, 'Italy': 48}"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cheese_consumption"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0      None\n",
       "1      None\n",
       "2      None\n",
       "3      None\n",
       "4      None\n",
       "       ... \n",
       "641    None\n",
       "642    None\n",
       "643    None\n",
       "644    None\n",
       "645    None\n",
       "Name: prizes, Length: 646, dtype: object"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def add_laureates_key(entry):\n",
    "    entry.setdefault(\"laureates\", [])\n",
    "\n",
    "nobel[\"prizes\"].apply(add_laureates_key)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>firstname</th>\n",
       "      <th>surname</th>\n",
       "      <th>motivation</th>\n",
       "      <th>share</th>\n",
       "      <th>year</th>\n",
       "      <th>category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>976</td>\n",
       "      <td>John</td>\n",
       "      <td>Goodenough</td>\n",
       "      <td>\"for the development of lithium-ion batteries\"</td>\n",
       "      <td>3</td>\n",
       "      <td>2019</td>\n",
       "      <td>chemistry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>977</td>\n",
       "      <td>M. Stanley</td>\n",
       "      <td>Whittingham</td>\n",
       "      <td>\"for the development of lithium-ion batteries\"</td>\n",
       "      <td>3</td>\n",
       "      <td>2019</td>\n",
       "      <td>chemistry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>978</td>\n",
       "      <td>Akira</td>\n",
       "      <td>Yoshino</td>\n",
       "      <td>\"for the development of lithium-ion batteries\"</td>\n",
       "      <td>3</td>\n",
       "      <td>2019</td>\n",
       "      <td>chemistry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>982</td>\n",
       "      <td>Abhijit</td>\n",
       "      <td>Banerjee</td>\n",
       "      <td>\"for their experimental approach to alleviatin...</td>\n",
       "      <td>3</td>\n",
       "      <td>2019</td>\n",
       "      <td>economics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>983</td>\n",
       "      <td>Esther</td>\n",
       "      <td>Duflo</td>\n",
       "      <td>\"for their experimental approach to alleviatin...</td>\n",
       "      <td>3</td>\n",
       "      <td>2019</td>\n",
       "      <td>economics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>945</th>\n",
       "      <td>569</td>\n",
       "      <td>Sully</td>\n",
       "      <td>Prudhomme</td>\n",
       "      <td>\"in special recognition of his poetic composit...</td>\n",
       "      <td>1</td>\n",
       "      <td>1901</td>\n",
       "      <td>literature</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>946</th>\n",
       "      <td>462</td>\n",
       "      <td>Henry</td>\n",
       "      <td>Dunant</td>\n",
       "      <td>\"for his humanitarian efforts to help wounded ...</td>\n",
       "      <td>2</td>\n",
       "      <td>1901</td>\n",
       "      <td>peace</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>947</th>\n",
       "      <td>463</td>\n",
       "      <td>Frédéric</td>\n",
       "      <td>Passy</td>\n",
       "      <td>\"for his lifelong work for international peace...</td>\n",
       "      <td>2</td>\n",
       "      <td>1901</td>\n",
       "      <td>peace</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>948</th>\n",
       "      <td>1</td>\n",
       "      <td>Wilhelm Conrad</td>\n",
       "      <td>Röntgen</td>\n",
       "      <td>\"in recognition of the extraordinary services ...</td>\n",
       "      <td>1</td>\n",
       "      <td>1901</td>\n",
       "      <td>physics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>949</th>\n",
       "      <td>293</td>\n",
       "      <td>Emil</td>\n",
       "      <td>von Behring</td>\n",
       "      <td>\"for his work on serum therapy, especially its...</td>\n",
       "      <td>1</td>\n",
       "      <td>1901</td>\n",
       "      <td>medicine</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>950 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      id       firstname      surname  \\\n",
       "0    976            John   Goodenough   \n",
       "1    977      M. Stanley  Whittingham   \n",
       "2    978           Akira      Yoshino   \n",
       "3    982         Abhijit     Banerjee   \n",
       "4    983          Esther        Duflo   \n",
       "..   ...             ...          ...   \n",
       "945  569           Sully    Prudhomme   \n",
       "946  462           Henry       Dunant   \n",
       "947  463        Frédéric        Passy   \n",
       "948    1  Wilhelm Conrad      Röntgen   \n",
       "949  293            Emil  von Behring   \n",
       "\n",
       "                                            motivation share  year    category  \n",
       "0       \"for the development of lithium-ion batteries\"     3  2019   chemistry  \n",
       "1       \"for the development of lithium-ion batteries\"     3  2019   chemistry  \n",
       "2       \"for the development of lithium-ion batteries\"     3  2019   chemistry  \n",
       "3    \"for their experimental approach to alleviatin...     3  2019   economics  \n",
       "4    \"for their experimental approach to alleviatin...     3  2019   economics  \n",
       "..                                                 ...   ...   ...         ...  \n",
       "945  \"in special recognition of his poetic composit...     1  1901  literature  \n",
       "946  \"for his humanitarian efforts to help wounded ...     2  1901       peace  \n",
       "947  \"for his lifelong work for international peace...     2  1901       peace  \n",
       "948  \"in recognition of the extraordinary services ...     1  1901     physics  \n",
       "949  \"for his work on serum therapy, especially its...     1  1901    medicine  \n",
       "\n",
       "[950 rows x 7 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "winners = pd.json_normalize(\n",
    "    data = nobel[\"prizes\"],\n",
    "    record_path = \"laureates\",\n",
    "    meta = [\"year\", \"category\"]\n",
    ")\n",
    "\n",
    "winners"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 12.1.2 Exporting a DataFrame to a JSON File"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>firstname</th>\n",
       "      <th>surname</th>\n",
       "      <th>motivation</th>\n",
       "      <th>share</th>\n",
       "      <th>year</th>\n",
       "      <th>category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>976</td>\n",
       "      <td>John</td>\n",
       "      <td>Goodenough</td>\n",
       "      <td>\"for the development of lithium-ion batteries\"</td>\n",
       "      <td>3</td>\n",
       "      <td>2019</td>\n",
       "      <td>chemistry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>977</td>\n",
       "      <td>M. Stanley</td>\n",
       "      <td>Whittingham</td>\n",
       "      <td>\"for the development of lithium-ion batteries\"</td>\n",
       "      <td>3</td>\n",
       "      <td>2019</td>\n",
       "      <td>chemistry</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    id   firstname      surname  \\\n",
       "0  976        John   Goodenough   \n",
       "1  977  M. Stanley  Whittingham   \n",
       "\n",
       "                                       motivation share  year   category  \n",
       "0  \"for the development of lithium-ion batteries\"     3  2019  chemistry  \n",
       "1  \"for the development of lithium-ion batteries\"     3  2019  chemistry  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "winners.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'[{\"id\":\"976\",\"firstname\":\"John\",\"surname\":\"Goodenough\",\"motivation\":\"\\\\\"for the development of lithium-ion batteries\\\\\"\",\"share\":\"3\",\"year\":\"2019\",\"category\":\"chemistry\"},{\"id\":\"977\",\"firstname\":\"M. Stanley\",\"surname\":\"Whittingham\",\"motivation\":\"\\\\\"for the development of lithium-ion batteries\\\\\"\",\"share\":\"3\",\"year\":\"2019\",\"category\":\"chemistry\"}]'"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "winners.head(2).to_json(orient = \"records\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'{\"columns\":[\"id\",\"firstname\",\"surname\",\"motivation\",\"share\",\"year\",\"category\"],\"index\":[0,1],\"data\":[[\"976\",\"John\",\"Goodenough\",\"\\\\\"for the development of lithium-ion batteries\\\\\"\",\"3\",\"2019\",\"chemistry\"],[\"977\",\"M. Stanley\",\"Whittingham\",\"\\\\\"for the development of lithium-ion batteries\\\\\"\",\"3\",\"2019\",\"chemistry\"]]}'"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "winners.head(2).to_json(orient = \"split\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "winners.to_json(\"winners.json\", orient = \"records\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 12.2 Reading from and Writing to CSV Files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year of Birth</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Ethnicity</th>\n",
       "      <th>Child's First Name</th>\n",
       "      <th>Count</th>\n",
       "      <th>Rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2011</td>\n",
       "      <td>FEMALE</td>\n",
       "      <td>HISPANIC</td>\n",
       "      <td>GERALDINE</td>\n",
       "      <td>13</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2011</td>\n",
       "      <td>FEMALE</td>\n",
       "      <td>HISPANIC</td>\n",
       "      <td>GIA</td>\n",
       "      <td>21</td>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2011</td>\n",
       "      <td>FEMALE</td>\n",
       "      <td>HISPANIC</td>\n",
       "      <td>GIANNA</td>\n",
       "      <td>49</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2011</td>\n",
       "      <td>FEMALE</td>\n",
       "      <td>HISPANIC</td>\n",
       "      <td>GISELLE</td>\n",
       "      <td>38</td>\n",
       "      <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2011</td>\n",
       "      <td>FEMALE</td>\n",
       "      <td>HISPANIC</td>\n",
       "      <td>GRACE</td>\n",
       "      <td>36</td>\n",
       "      <td>53</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year of Birth  Gender Ethnicity Child's First Name  Count  Rank\n",
       "0           2011  FEMALE  HISPANIC          GERALDINE     13    75\n",
       "1           2011  FEMALE  HISPANIC                GIA     21    67\n",
       "2           2011  FEMALE  HISPANIC             GIANNA     49    42\n",
       "3           2011  FEMALE  HISPANIC            GISELLE     38    51\n",
       "4           2011  FEMALE  HISPANIC              GRACE     36    53"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "url = \"https://data.cityofnewyork.us/api/views/25th-nujf/rows.csv\"\n",
    "baby_names = pd.read_csv(url)\n",
    "baby_names.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\",Year of Birth,Gender,Ethnicity,Child's First Name,Count,Rank\\n0,2011,FEMALE,HISPANIC,GERALDINE,13,75\\n1,2011,FEMALE,HISPANIC,GIA,21,67\\n2,2011,FEMALE,HISPANIC,GIANNA,49,42\\n3,2011,FEMALE,HISPANIC,GISELLE,38,51\\n4,2011,FEMALE,HISPANIC,GRACE,36,53\\n5,2011,FEMALE,HISPANIC,GUADALUPE,26,62\\n6,2011,FEMALE,HISPANIC,HAILEY,126,8\\n7,2011,FEMALE,HISPANIC,HALEY,14,74\\n8,2011,FEMALE,HISPANIC,HANNAH,17,71\\n9,2011,FEMALE,HISPANIC,HAYLEE,17,71\\n\""
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "baby_names.head(10).to_csv()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"Year of Birth,Gender,Ethnicity,Child's First Name,Count,Rank\\n2011,FEMALE,HISPANIC,GERALDINE,13,75\\n2011,FEMALE,HISPANIC,GIA,21,67\\n2011,FEMALE,HISPANIC,GIANNA,49,42\\n2011,FEMALE,HISPANIC,GISELLE,38,51\\n2011,FEMALE,HISPANIC,GRACE,36,53\\n2011,FEMALE,HISPANIC,GUADALUPE,26,62\\n2011,FEMALE,HISPANIC,HAILEY,126,8\\n2011,FEMALE,HISPANIC,HALEY,14,74\\n2011,FEMALE,HISPANIC,HANNAH,17,71\\n2011,FEMALE,HISPANIC,HAYLEE,17,71\\n\""
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "baby_names.head(10).to_csv(index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "baby_names.to_csv(\"NYC_Baby_Names.csv\", index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "baby_names.to_csv(\n",
    "    \"NYC_Baby_Names.csv\",\n",
    "    index = False, \n",
    "    columns = [\"Gender\", \"Child's First Name\", \"Count\"]\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 12.3 Reading from and Writing to Excel Workbooks"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>First Name</th>\n",
       "      <th>Last Name</th>\n",
       "      <th>City</th>\n",
       "      <th>Gender</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Brandon</td>\n",
       "      <td>James</td>\n",
       "      <td>Miami</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Sean</td>\n",
       "      <td>Hawkins</td>\n",
       "      <td>Denver</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Judy</td>\n",
       "      <td>Day</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ashley</td>\n",
       "      <td>Ruiz</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Stephanie</td>\n",
       "      <td>Gomez</td>\n",
       "      <td>Portland</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  First Name Last Name           City Gender\n",
       "0    Brandon     James          Miami      M\n",
       "1       Sean   Hawkins         Denver      M\n",
       "2       Judy       Day    Los Angeles      F\n",
       "3     Ashley      Ruiz  San Francisco      F\n",
       "4  Stephanie     Gomez       Portland      F"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_excel(\"Single Worksheet.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>First Name</th>\n",
       "      <th>Last Name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>City</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Miami</th>\n",
       "      <td>Brandon</td>\n",
       "      <td>James</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Denver</th>\n",
       "      <td>Sean</td>\n",
       "      <td>Hawkins</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Los Angeles</th>\n",
       "      <td>Judy</td>\n",
       "      <td>Day</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>San Francisco</th>\n",
       "      <td>Ashley</td>\n",
       "      <td>Ruiz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Portland</th>\n",
       "      <td>Stephanie</td>\n",
       "      <td>Gomez</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              First Name Last Name\n",
       "City                              \n",
       "Miami            Brandon     James\n",
       "Denver              Sean   Hawkins\n",
       "Los Angeles         Judy       Day\n",
       "San Francisco     Ashley      Ruiz\n",
       "Portland       Stephanie     Gomez"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_excel(\n",
    "    io = \"Single Worksheet.xlsx\",\n",
    "    usecols = [\"City\", \"First Name\", \"Last Name\"],\n",
    "    index_col = \"City\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>First Name</th>\n",
       "      <th>Last Name</th>\n",
       "      <th>City</th>\n",
       "      <th>Gender</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Brandon</td>\n",
       "      <td>James</td>\n",
       "      <td>Miami</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Sean</td>\n",
       "      <td>Hawkins</td>\n",
       "      <td>Denver</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Judy</td>\n",
       "      <td>Day</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ashley</td>\n",
       "      <td>Ruiz</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Stephanie</td>\n",
       "      <td>Gomez</td>\n",
       "      <td>Portland</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  First Name Last Name           City Gender\n",
       "0    Brandon     James          Miami      M\n",
       "1       Sean   Hawkins         Denver      M\n",
       "2       Judy       Day    Los Angeles      F\n",
       "3     Ashley      Ruiz  San Francisco      F\n",
       "4  Stephanie     Gomez       Portland      F"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_excel(\"Multiple Worksheets.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>First Name</th>\n",
       "      <th>Last Name</th>\n",
       "      <th>City</th>\n",
       "      <th>Gender</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Brandon</td>\n",
       "      <td>James</td>\n",
       "      <td>Miami</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Sean</td>\n",
       "      <td>Hawkins</td>\n",
       "      <td>Denver</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Judy</td>\n",
       "      <td>Day</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ashley</td>\n",
       "      <td>Ruiz</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Stephanie</td>\n",
       "      <td>Gomez</td>\n",
       "      <td>Portland</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  First Name Last Name           City Gender\n",
       "0    Brandon     James          Miami      M\n",
       "1       Sean   Hawkins         Denver      M\n",
       "2       Judy       Day    Los Angeles      F\n",
       "3     Ashley      Ruiz  San Francisco      F\n",
       "4  Stephanie     Gomez       Portland      F"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# The two lines below are equivalent\n",
    "pd.read_excel(\"Multiple Worksheets.xlsx\", sheet_name = 0)\n",
    "pd.read_excel(\"Multiple Worksheets.xlsx\", sheet_name = \"Data 1\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Data 1':   First Name Last Name           City Gender\n",
       " 0    Brandon     James          Miami      M\n",
       " 1       Sean   Hawkins         Denver      M\n",
       " 2       Judy       Day    Los Angeles      F\n",
       " 3     Ashley      Ruiz  San Francisco      F\n",
       " 4  Stephanie     Gomez       Portland      F,\n",
       " 'Data 2':   First Name Last Name           City Gender\n",
       " 0     Parker     Power        Raleigh      F\n",
       " 1    Preston  Prescott   Philadelphia      F\n",
       " 2    Ronaldo   Donaldo         Bangor      M\n",
       " 3      Megan   Stiller  San Francisco      M\n",
       " 4     Bustin    Jieber         Austin      F,\n",
       " 'Data 3':   First Name  Last Name     City Gender\n",
       " 0     Robert     Miller  Seattle      M\n",
       " 1       Tara     Garcia  Phoenix      F\n",
       " 2    Raphael  Rodriguez  Orlando      M}"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "workbook = pd.read_excel(\n",
    "    \"Multiple Worksheets.xlsx\", sheet_name = None\n",
    ")\n",
    "\n",
    "workbook"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(workbook)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>First Name</th>\n",
       "      <th>Last Name</th>\n",
       "      <th>City</th>\n",
       "      <th>Gender</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Parker</td>\n",
       "      <td>Power</td>\n",
       "      <td>Raleigh</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Preston</td>\n",
       "      <td>Prescott</td>\n",
       "      <td>Philadelphia</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Ronaldo</td>\n",
       "      <td>Donaldo</td>\n",
       "      <td>Bangor</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Megan</td>\n",
       "      <td>Stiller</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Bustin</td>\n",
       "      <td>Jieber</td>\n",
       "      <td>Austin</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  First Name Last Name           City Gender\n",
       "0     Parker     Power        Raleigh      F\n",
       "1    Preston  Prescott   Philadelphia      F\n",
       "2    Ronaldo   Donaldo         Bangor      M\n",
       "3      Megan   Stiller  San Francisco      M\n",
       "4     Bustin    Jieber         Austin      F"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "workbook[\"Data 2\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Data 1':   First Name Last Name           City Gender\n",
       " 0    Brandon     James          Miami      M\n",
       " 1       Sean   Hawkins         Denver      M\n",
       " 2       Judy       Day    Los Angeles      F\n",
       " 3     Ashley      Ruiz  San Francisco      F\n",
       " 4  Stephanie     Gomez       Portland      F,\n",
       " 'Data 3':   First Name  Last Name     City Gender\n",
       " 0     Robert     Miller  Seattle      M\n",
       " 1       Tara     Garcia  Phoenix      F\n",
       " 2    Raphael  Rodriguez  Orlando      M}"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_excel(\n",
    "    \"Multiple Worksheets.xlsx\",\n",
    "    sheet_name = [\"Data 1\", \"Data 3\"]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{1:   First Name Last Name           City Gender\n",
       " 0     Parker     Power        Raleigh      F\n",
       " 1    Preston  Prescott   Philadelphia      F\n",
       " 2    Ronaldo   Donaldo         Bangor      M\n",
       " 3      Megan   Stiller  San Francisco      M\n",
       " 4     Bustin    Jieber         Austin      F,\n",
       " 2:   First Name  Last Name     City Gender\n",
       " 0     Robert     Miller  Seattle      M\n",
       " 1       Tara     Garcia  Phoenix      F\n",
       " 2    Raphael  Rodriguez  Orlando      M}"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_excel(\"Multiple Worksheets.xlsx\", sheet_name = [1, 2])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 12.3.3 Exporting Excel Workbooks"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year of Birth</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Ethnicity</th>\n",
       "      <th>Child's First Name</th>\n",
       "      <th>Count</th>\n",
       "      <th>Rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2011</td>\n",
       "      <td>FEMALE</td>\n",
       "      <td>HISPANIC</td>\n",
       "      <td>GERALDINE</td>\n",
       "      <td>13</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2011</td>\n",
       "      <td>FEMALE</td>\n",
       "      <td>HISPANIC</td>\n",
       "      <td>GIA</td>\n",
       "      <td>21</td>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2011</td>\n",
       "      <td>FEMALE</td>\n",
       "      <td>HISPANIC</td>\n",
       "      <td>GIANNA</td>\n",
       "      <td>49</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2011</td>\n",
       "      <td>FEMALE</td>\n",
       "      <td>HISPANIC</td>\n",
       "      <td>GISELLE</td>\n",
       "      <td>38</td>\n",
       "      <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2011</td>\n",
       "      <td>FEMALE</td>\n",
       "      <td>HISPANIC</td>\n",
       "      <td>GRACE</td>\n",
       "      <td>36</td>\n",
       "      <td>53</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year of Birth  Gender Ethnicity Child's First Name  Count  Rank\n",
       "0           2011  FEMALE  HISPANIC          GERALDINE     13    75\n",
       "1           2011  FEMALE  HISPANIC                GIA     21    67\n",
       "2           2011  FEMALE  HISPANIC             GIANNA     49    42\n",
       "3           2011  FEMALE  HISPANIC            GISELLE     38    51\n",
       "4           2011  FEMALE  HISPANIC              GRACE     36    53"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "baby_names.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "girls = baby_names[baby_names[\"Gender\"] == \"FEMALE\"]\n",
    "boys = baby_names[baby_names[\"Gender\"] == \"MALE\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pandas.io.excel._openpyxl.OpenpyxlWriter at 0x7fc013976430>"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "excel_file = pd.ExcelWriter(\"Baby_Names.xlsx\")\n",
    "excel_file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "girls.to_excel(\n",
    "    excel_writer = excel_file, sheet_name = \"Girls\", index = False\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "boys.to_excel(\n",
    "    excel_file,\n",
    "    sheet_name = \"Boys\",\n",
    "    index = False,\n",
    "    columns = [\"Child's First Name\", \"Count\", \"Rank\"]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "excel_file.save()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 12.4 Coding Challenge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>shows</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>{'show': 'The X-Files', 'runtime': 60, 'networ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>{'show': 'Lost', 'runtime': 60, 'network': 'AB...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>{'show': 'Buffy the Vampire Slayer', 'runtime'...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                               shows\n",
       "0  {'show': 'The X-Files', 'runtime': 60, 'networ...\n",
       "1  {'show': 'Lost', 'runtime': 60, 'network': 'AB...\n",
       "2  {'show': 'Buffy the Vampire Slayer', 'runtime'..."
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tv_shows_json = pd.read_json(\"tv_shows.json\")\n",
    "tv_shows_json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'show': 'The X-Files',\n",
       " 'runtime': 60,\n",
       " 'network': 'FOX',\n",
       " 'episodes': [{'season': 1,\n",
       "   'episode': 1,\n",
       "   'name': 'Pilot',\n",
       "   'air_date': '1993-09-11 01:00:00'},\n",
       "  {'season': 1,\n",
       "   'episode': 2,\n",
       "   'name': 'Deep Throat',\n",
       "   'air_date': '1993-09-18 01:00:00'},\n",
       "  {'season': 1,\n",
       "   'episode': 3,\n",
       "   'name': 'Squeeze',\n",
       "   'air_date': '1993-09-25 01:00:00'},\n",
       "  {'season': 1,\n",
       "   'episode': 4,\n",
       "   'name': 'Conduit',\n",
       "   'air_date': '1993-10-02 01:00:00'},\n",
       "  {'season': 1,\n",
       "   'episode': 5,\n",
       "   'name': 'The Jersey Devil',\n",
       "   'air_date': '1993-10-09 01:00:00'},\n",
       "  {'season': 1,\n",
       "   'episode': 6,\n",
       "   'name': 'Shadows',\n",
       "   'air_date': '1993-10-23 01:00:00'},\n",
       "  {'season': 1,\n",
       "   'episode': 7,\n",
       "   'name': 'Ghost in the Machine',\n",
       "   'air_date': '1993-10-30 01:00:00'},\n",
       "  {'season': 1,\n",
       "   'episode': 8,\n",
       "   'name': 'Ice',\n",
       "   'air_date': '1993-11-06 02:00:00'},\n",
       "  {'season': 1,\n",
       "   'episode': 9,\n",
       "   'name': 'Space',\n",
       "   'air_date': '1993-11-13 02:00:00'},\n",
       "  {'season': 1,\n",
       "   'episode': 10,\n",
       "   'name': 'Fallen Angel',\n",
       "   'air_date': '1993-11-20 02:00:00'},\n",
       "  {'season': 1,\n",
       "   'episode': 11,\n",
       "   'name': 'Eve',\n",
       "   'air_date': '1993-12-11 02:00:00'},\n",
       "  {'season': 1,\n",
       "   'episode': 12,\n",
       "   'name': 'Fire',\n",
       "   'air_date': '1993-12-18 02:00:00'},\n",
       "  {'season': 1,\n",
       "   'episode': 13,\n",
       "   'name': 'Beyond the Sea',\n",
       "   'air_date': '1994-01-08 02:00:00'},\n",
       "  {'season': 1,\n",
       "   'episode': 14,\n",
       "   'name': 'Gender Bender',\n",
       "   'air_date': '1994-01-22 02:00:00'},\n",
       "  {'season': 1,\n",
       "   'episode': 15,\n",
       "   'name': 'Lazarus',\n",
       "   'air_date': '1994-02-05 02:00:00'},\n",
       "  {'season': 1,\n",
       "   'episode': 16,\n",
       "   'name': 'Young at Heart',\n",
       "   'air_date': '1994-02-12 02:00:00'},\n",
       "  {'season': 1,\n",
       "   'episode': 17,\n",
       "   'name': 'E.B.E.',\n",
       "   'air_date': '1994-02-19 02:00:00'},\n",
       "  {'season': 1,\n",
       "   'episode': 18,\n",
       "   'name': 'Miracle Man',\n",
       "   'air_date': '1994-03-19 02:00:00'},\n",
       "  {'season': 1,\n",
       "   'episode': 19,\n",
       "   'name': 'Shapes',\n",
       "   'air_date': '1994-04-02 02:00:00'},\n",
       "  {'season': 1,\n",
       "   'episode': 20,\n",
       "   'name': 'Darkness Falls',\n",
       "   'air_date': '1994-04-16 01:00:00'},\n",
       "  {'season': 1,\n",
       "   'episode': 21,\n",
       "   'name': 'Tooms',\n",
       "   'air_date': '1994-04-23 01:00:00'},\n",
       "  {'season': 1,\n",
       "   'episode': 22,\n",
       "   'name': 'Born Again',\n",
       "   'air_date': '1994-04-30 01:00:00'},\n",
       "  {'season': 1,\n",
       "   'episode': 23,\n",
       "   'name': 'Roland',\n",
       "   'air_date': '1994-05-07 01:00:00'},\n",
       "  {'season': 1,\n",
       "   'episode': 24,\n",
       "   'name': 'The Erlenmeyer Flask',\n",
       "   'air_date': '1994-05-14 01:00:00'},\n",
       "  {'season': 2,\n",
       "   'episode': 1,\n",
       "   'name': 'Little Green Men',\n",
       "   'air_date': '1994-09-17 01:00:00'},\n",
       "  {'season': 2,\n",
       "   'episode': 2,\n",
       "   'name': 'The Host',\n",
       "   'air_date': '1994-09-24 01:00:00'},\n",
       "  {'season': 2,\n",
       "   'episode': 3,\n",
       "   'name': 'Blood',\n",
       "   'air_date': '1994-10-01 01:00:00'},\n",
       "  {'season': 2,\n",
       "   'episode': 4,\n",
       "   'name': 'Sleepless',\n",
       "   'air_date': '1994-10-08 01:00:00'},\n",
       "  {'season': 2,\n",
       "   'episode': 5,\n",
       "   'name': 'Duane Barry (1)',\n",
       "   'air_date': '1994-10-15 01:00:00'},\n",
       "  {'season': 2,\n",
       "   'episode': 6,\n",
       "   'name': 'Ascension (2)',\n",
       "   'air_date': '1994-10-22 01:00:00'},\n",
       "  {'season': 2, 'episode': 7, 'name': '3', 'air_date': '1994-11-05 02:00:00'},\n",
       "  {'season': 2,\n",
       "   'episode': 8,\n",
       "   'name': 'One Breath',\n",
       "   'air_date': '1994-11-12 02:00:00'},\n",
       "  {'season': 2,\n",
       "   'episode': 9,\n",
       "   'name': 'Firewalker',\n",
       "   'air_date': '1994-11-19 02:00:00'},\n",
       "  {'season': 2,\n",
       "   'episode': 10,\n",
       "   'name': 'Red Museum',\n",
       "   'air_date': '1994-12-10 02:00:00'},\n",
       "  {'season': 2,\n",
       "   'episode': 11,\n",
       "   'name': 'Excelsis Dei',\n",
       "   'air_date': '1994-12-17 02:00:00'},\n",
       "  {'season': 2,\n",
       "   'episode': 12,\n",
       "   'name': 'Aubrey',\n",
       "   'air_date': '1995-01-07 02:00:00'},\n",
       "  {'season': 2,\n",
       "   'episode': 13,\n",
       "   'name': 'Irresistible',\n",
       "   'air_date': '1995-01-14 02:00:00'},\n",
       "  {'season': 2,\n",
       "   'episode': 14,\n",
       "   'name': 'Die Hand die Verletzt',\n",
       "   'air_date': '1995-01-28 02:00:00'},\n",
       "  {'season': 2,\n",
       "   'episode': 15,\n",
       "   'name': 'Fresh Bones',\n",
       "   'air_date': '1995-02-04 02:00:00'},\n",
       "  {'season': 2,\n",
       "   'episode': 16,\n",
       "   'name': 'Colony (1)',\n",
       "   'air_date': '1995-02-11 02:00:00'},\n",
       "  {'season': 2,\n",
       "   'episode': 17,\n",
       "   'name': 'End Game (2)',\n",
       "   'air_date': '1995-02-18 02:00:00'},\n",
       "  {'season': 2,\n",
       "   'episode': 18,\n",
       "   'name': 'Fearful Symmetry',\n",
       "   'air_date': '1995-02-25 02:00:00'},\n",
       "  {'season': 2,\n",
       "   'episode': 19,\n",
       "   'name': 'Død Kalm',\n",
       "   'air_date': '1995-03-11 02:00:00'},\n",
       "  {'season': 2,\n",
       "   'episode': 20,\n",
       "   'name': 'Humbug',\n",
       "   'air_date': '1995-04-01 02:00:00'},\n",
       "  {'season': 2,\n",
       "   'episode': 21,\n",
       "   'name': 'The Calusari',\n",
       "   'air_date': '1995-04-15 01:00:00'},\n",
       "  {'season': 2,\n",
       "   'episode': 22,\n",
       "   'name': 'F. Emasculata',\n",
       "   'air_date': '1995-04-29 01:00:00'},\n",
       "  {'season': 2,\n",
       "   'episode': 23,\n",
       "   'name': 'Soft Light',\n",
       "   'air_date': '1995-05-06 01:00:00'},\n",
       "  {'season': 2,\n",
       "   'episode': 24,\n",
       "   'name': 'Our Town',\n",
       "   'air_date': '1995-05-13 01:00:00'},\n",
       "  {'season': 2,\n",
       "   'episode': 25,\n",
       "   'name': 'Anasazi',\n",
       "   'air_date': '1995-05-20 01:00:00'},\n",
       "  {'season': 3,\n",
       "   'episode': 1,\n",
       "   'name': 'The Blessing Way',\n",
       "   'air_date': '1995-09-23 01:00:00'},\n",
       "  {'season': 3,\n",
       "   'episode': 2,\n",
       "   'name': 'Paper Clip',\n",
       "   'air_date': '1995-09-30 01:00:00'},\n",
       "  {'season': 3,\n",
       "   'episode': 3,\n",
       "   'name': 'D.P.O.',\n",
       "   'air_date': '1995-10-07 01:00:00'},\n",
       "  {'season': 3,\n",
       "   'episode': 4,\n",
       "   'name': \"Clyde Bruckman's Final Repose\",\n",
       "   'air_date': '1995-10-14 01:00:00'},\n",
       "  {'season': 3,\n",
       "   'episode': 5,\n",
       "   'name': 'The List',\n",
       "   'air_date': '1995-10-21 01:00:00'},\n",
       "  {'season': 3,\n",
       "   'episode': 6,\n",
       "   'name': '2Shy',\n",
       "   'air_date': '1995-11-04 02:00:00'},\n",
       "  {'season': 3,\n",
       "   'episode': 7,\n",
       "   'name': 'The Walk',\n",
       "   'air_date': '1995-11-11 02:00:00'},\n",
       "  {'season': 3,\n",
       "   'episode': 8,\n",
       "   'name': 'Oubliette',\n",
       "   'air_date': '1995-11-18 02:00:00'},\n",
       "  {'season': 3,\n",
       "   'episode': 9,\n",
       "   'name': 'Nisei (1)',\n",
       "   'air_date': '1995-11-25 02:00:00'},\n",
       "  {'season': 3,\n",
       "   'episode': 10,\n",
       "   'name': '731 (2)',\n",
       "   'air_date': '1995-12-02 02:00:00'},\n",
       "  {'season': 3,\n",
       "   'episode': 11,\n",
       "   'name': 'Revelations',\n",
       "   'air_date': '1995-12-16 02:00:00'},\n",
       "  {'season': 3,\n",
       "   'episode': 12,\n",
       "   'name': 'War of the Coprophages',\n",
       "   'air_date': '1996-01-06 02:00:00'},\n",
       "  {'season': 3,\n",
       "   'episode': 13,\n",
       "   'name': 'Syzygy',\n",
       "   'air_date': '1996-01-27 02:00:00'},\n",
       "  {'season': 3,\n",
       "   'episode': 14,\n",
       "   'name': 'Grotesque',\n",
       "   'air_date': '1996-02-03 02:00:00'},\n",
       "  {'season': 3,\n",
       "   'episode': 15,\n",
       "   'name': 'Piper Maru (1)',\n",
       "   'air_date': '1996-02-10 02:00:00'},\n",
       "  {'season': 3,\n",
       "   'episode': 16,\n",
       "   'name': 'Apocrypha (2)',\n",
       "   'air_date': '1996-02-17 02:00:00'},\n",
       "  {'season': 3,\n",
       "   'episode': 17,\n",
       "   'name': 'Pusher',\n",
       "   'air_date': '1996-02-24 02:00:00'},\n",
       "  {'season': 3,\n",
       "   'episode': 18,\n",
       "   'name': 'Teso dos Bichos',\n",
       "   'air_date': '1996-03-09 02:00:00'},\n",
       "  {'season': 3,\n",
       "   'episode': 19,\n",
       "   'name': 'Hell Money',\n",
       "   'air_date': '1996-03-30 02:00:00'},\n",
       "  {'season': 3,\n",
       "   'episode': 20,\n",
       "   'name': \"Jose Chung's 'From Outer Space'\",\n",
       "   'air_date': '1996-04-13 01:00:00'},\n",
       "  {'season': 3,\n",
       "   'episode': 21,\n",
       "   'name': 'Avatar',\n",
       "   'air_date': '1996-04-27 01:00:00'},\n",
       "  {'season': 3,\n",
       "   'episode': 22,\n",
       "   'name': 'Quagmire',\n",
       "   'air_date': '1996-05-04 01:00:00'},\n",
       "  {'season': 3,\n",
       "   'episode': 23,\n",
       "   'name': 'Wetwired',\n",
       "   'air_date': '1996-05-11 01:00:00'},\n",
       "  {'season': 3,\n",
       "   'episode': 24,\n",
       "   'name': 'Talitha Cumi (1)',\n",
       "   'air_date': '1996-05-18 01:00:00'},\n",
       "  {'season': 4,\n",
       "   'episode': 1,\n",
       "   'name': 'Herrenvolk (2)',\n",
       "   'air_date': '1996-10-05 01:00:00'},\n",
       "  {'season': 4,\n",
       "   'episode': 2,\n",
       "   'name': 'Home',\n",
       "   'air_date': '1996-10-12 01:00:00'},\n",
       "  {'season': 4,\n",
       "   'episode': 3,\n",
       "   'name': 'Teliko',\n",
       "   'air_date': '1996-10-19 01:00:00'},\n",
       "  {'season': 4,\n",
       "   'episode': 4,\n",
       "   'name': 'Unruhe',\n",
       "   'air_date': '1996-10-28 02:00:00'},\n",
       "  {'season': 4,\n",
       "   'episode': 5,\n",
       "   'name': 'The Field Where I Died',\n",
       "   'air_date': '1996-11-04 02:00:00'},\n",
       "  {'season': 4,\n",
       "   'episode': 6,\n",
       "   'name': 'Sanguinarium',\n",
       "   'air_date': '1996-11-11 02:00:00'},\n",
       "  {'season': 4,\n",
       "   'episode': 7,\n",
       "   'name': 'Musings of a Cigarette-Smoking Man',\n",
       "   'air_date': '1996-11-18 02:00:00'},\n",
       "  {'season': 4,\n",
       "   'episode': 8,\n",
       "   'name': 'Tunguska (1)',\n",
       "   'air_date': '1996-11-25 02:00:00'},\n",
       "  {'season': 4,\n",
       "   'episode': 9,\n",
       "   'name': 'Terma (2)',\n",
       "   'air_date': '1996-12-02 02:00:00'},\n",
       "  {'season': 4,\n",
       "   'episode': 10,\n",
       "   'name': 'Paper Hearts',\n",
       "   'air_date': '1996-12-16 02:00:00'},\n",
       "  {'season': 4,\n",
       "   'episode': 11,\n",
       "   'name': 'El Mundo Gira',\n",
       "   'air_date': '1997-01-13 02:00:00'},\n",
       "  {'season': 4,\n",
       "   'episode': 12,\n",
       "   'name': 'Leonard Betts',\n",
       "   'air_date': '1997-01-27 02:00:00'},\n",
       "  {'season': 4,\n",
       "   'episode': 13,\n",
       "   'name': 'Never Again',\n",
       "   'air_date': '1997-02-03 02:00:00'},\n",
       "  {'season': 4,\n",
       "   'episode': 14,\n",
       "   'name': 'Memento Mori',\n",
       "   'air_date': '1997-02-10 02:00:00'},\n",
       "  {'season': 4,\n",
       "   'episode': 15,\n",
       "   'name': 'Kaddish',\n",
       "   'air_date': '1997-02-17 02:00:00'},\n",
       "  {'season': 4,\n",
       "   'episode': 16,\n",
       "   'name': 'Unrequited',\n",
       "   'air_date': '1997-02-24 02:00:00'},\n",
       "  {'season': 4,\n",
       "   'episode': 17,\n",
       "   'name': 'Tempus Fugit (1)',\n",
       "   'air_date': '1997-03-17 02:00:00'},\n",
       "  {'season': 4,\n",
       "   'episode': 18,\n",
       "   'name': 'Max (2)',\n",
       "   'air_date': '1997-03-24 02:00:00'},\n",
       "  {'season': 4,\n",
       "   'episode': 19,\n",
       "   'name': 'Synchrony',\n",
       "   'air_date': '1997-04-14 01:00:00'},\n",
       "  {'season': 4,\n",
       "   'episode': 20,\n",
       "   'name': 'Small Potatoes',\n",
       "   'air_date': '1997-04-21 01:00:00'},\n",
       "  {'season': 4,\n",
       "   'episode': 21,\n",
       "   'name': 'Zero Sum',\n",
       "   'air_date': '1997-04-28 01:00:00'},\n",
       "  {'season': 4,\n",
       "   'episode': 22,\n",
       "   'name': 'Elegy',\n",
       "   'air_date': '1997-05-05 01:00:00'},\n",
       "  {'season': 4,\n",
       "   'episode': 23,\n",
       "   'name': 'Demons',\n",
       "   'air_date': '1997-05-12 01:00:00'},\n",
       "  {'season': 4,\n",
       "   'episode': 24,\n",
       "   'name': 'Gethsemane',\n",
       "   'air_date': '1997-05-19 01:00:00'},\n",
       "  {'season': 5,\n",
       "   'episode': 1,\n",
       "   'name': 'Redux (1)',\n",
       "   'air_date': '1997-11-03 02:00:00'},\n",
       "  {'season': 5,\n",
       "   'episode': 2,\n",
       "   'name': 'Redux (2)',\n",
       "   'air_date': '1997-11-10 02:00:00'},\n",
       "  {'season': 5,\n",
       "   'episode': 3,\n",
       "   'name': 'Unusual Suspects',\n",
       "   'air_date': '1997-11-17 02:00:00'},\n",
       "  {'season': 5,\n",
       "   'episode': 4,\n",
       "   'name': 'Detour',\n",
       "   'air_date': '1997-11-24 02:00:00'},\n",
       "  {'season': 5,\n",
       "   'episode': 5,\n",
       "   'name': 'The Post-Modern Prometheus',\n",
       "   'air_date': '1997-12-01 02:00:00'},\n",
       "  {'season': 5,\n",
       "   'episode': 6,\n",
       "   'name': 'Christmas Carol (1)',\n",
       "   'air_date': '1997-12-08 02:00:00'},\n",
       "  {'season': 5,\n",
       "   'episode': 7,\n",
       "   'name': 'Emily (2)',\n",
       "   'air_date': '1997-12-15 02:00:00'},\n",
       "  {'season': 5,\n",
       "   'episode': 8,\n",
       "   'name': 'Kitsunegari',\n",
       "   'air_date': '1998-01-05 02:00:00'},\n",
       "  {'season': 5,\n",
       "   'episode': 9,\n",
       "   'name': 'Schizogeny',\n",
       "   'air_date': '1998-01-12 02:00:00'},\n",
       "  {'season': 5,\n",
       "   'episode': 10,\n",
       "   'name': 'Chinga',\n",
       "   'air_date': '1998-02-09 02:00:00'},\n",
       "  {'season': 5,\n",
       "   'episode': 11,\n",
       "   'name': 'Kill Switch',\n",
       "   'air_date': '1998-02-16 02:00:00'},\n",
       "  {'season': 5,\n",
       "   'episode': 12,\n",
       "   'name': 'Bad Blood',\n",
       "   'air_date': '1998-02-23 02:00:00'},\n",
       "  {'season': 5,\n",
       "   'episode': 13,\n",
       "   'name': 'Patient X (1)',\n",
       "   'air_date': '1998-03-02 02:00:00'},\n",
       "  {'season': 5,\n",
       "   'episode': 14,\n",
       "   'name': 'The Red and the Black (2)',\n",
       "   'air_date': '1998-03-09 02:00:00'},\n",
       "  {'season': 5,\n",
       "   'episode': 15,\n",
       "   'name': 'Travelers',\n",
       "   'air_date': '1998-03-30 02:00:00'},\n",
       "  {'season': 5,\n",
       "   'episode': 16,\n",
       "   'name': \"Mind's Eye\",\n",
       "   'air_date': '1998-04-20 01:00:00'},\n",
       "  {'season': 5,\n",
       "   'episode': 17,\n",
       "   'name': 'All Souls',\n",
       "   'air_date': '1998-04-27 01:00:00'},\n",
       "  {'season': 5,\n",
       "   'episode': 18,\n",
       "   'name': 'The Pine Bluff Variant',\n",
       "   'air_date': '1998-05-04 01:00:00'},\n",
       "  {'season': 5,\n",
       "   'episode': 19,\n",
       "   'name': 'Folie a Deux',\n",
       "   'air_date': '1998-05-11 01:00:00'},\n",
       "  {'season': 5,\n",
       "   'episode': 20,\n",
       "   'name': 'The End',\n",
       "   'air_date': '1998-05-18 01:00:00'},\n",
       "  {'season': 6,\n",
       "   'episode': 1,\n",
       "   'name': 'The Beginning',\n",
       "   'air_date': '1998-11-09 02:00:00'},\n",
       "  {'season': 6,\n",
       "   'episode': 2,\n",
       "   'name': 'Drive',\n",
       "   'air_date': '1998-11-16 02:00:00'},\n",
       "  {'season': 6,\n",
       "   'episode': 3,\n",
       "   'name': 'Triangle',\n",
       "   'air_date': '1998-11-23 02:00:00'},\n",
       "  {'season': 6,\n",
       "   'episode': 4,\n",
       "   'name': 'Dreamland (1)',\n",
       "   'air_date': '1998-11-30 02:00:00'},\n",
       "  {'season': 6,\n",
       "   'episode': 5,\n",
       "   'name': 'Dreamland (2)',\n",
       "   'air_date': '1998-12-07 02:00:00'},\n",
       "  {'season': 6,\n",
       "   'episode': 6,\n",
       "   'name': 'How the Ghosts Stole Christmas',\n",
       "   'air_date': '1998-12-14 02:00:00'},\n",
       "  {'season': 6,\n",
       "   'episode': 7,\n",
       "   'name': 'Terms of Endearment',\n",
       "   'air_date': '1999-01-04 02:00:00'},\n",
       "  {'season': 6,\n",
       "   'episode': 8,\n",
       "   'name': 'The Rain King',\n",
       "   'air_date': '1999-01-11 02:00:00'},\n",
       "  {'season': 6,\n",
       "   'episode': 9,\n",
       "   'name': 'S.R. 819',\n",
       "   'air_date': '1999-01-18 02:00:00'},\n",
       "  {'season': 6,\n",
       "   'episode': 10,\n",
       "   'name': 'Tithonus',\n",
       "   'air_date': '1999-01-25 02:00:00'},\n",
       "  {'season': 6,\n",
       "   'episode': 11,\n",
       "   'name': 'Two Fathers (1)',\n",
       "   'air_date': '1999-02-08 02:00:00'},\n",
       "  {'season': 6,\n",
       "   'episode': 12,\n",
       "   'name': 'One Son (2)',\n",
       "   'air_date': '1999-02-15 02:00:00'},\n",
       "  {'season': 6,\n",
       "   'episode': 13,\n",
       "   'name': 'Agua Mala',\n",
       "   'air_date': '1999-02-22 02:00:00'},\n",
       "  {'season': 6,\n",
       "   'episode': 14,\n",
       "   'name': 'Monday',\n",
       "   'air_date': '1999-03-01 02:00:00'},\n",
       "  {'season': 6,\n",
       "   'episode': 15,\n",
       "   'name': 'Arcadia',\n",
       "   'air_date': '1999-03-08 02:00:00'},\n",
       "  {'season': 6,\n",
       "   'episode': 16,\n",
       "   'name': 'Alpha',\n",
       "   'air_date': '1999-03-29 02:00:00'},\n",
       "  {'season': 6,\n",
       "   'episode': 17,\n",
       "   'name': 'Trevor',\n",
       "   'air_date': '1999-04-12 01:00:00'},\n",
       "  {'season': 6,\n",
       "   'episode': 18,\n",
       "   'name': 'Milagro',\n",
       "   'air_date': '1999-04-19 01:00:00'},\n",
       "  {'season': 6,\n",
       "   'episode': 19,\n",
       "   'name': 'The Unnatural',\n",
       "   'air_date': '1999-04-26 01:00:00'},\n",
       "  {'season': 6,\n",
       "   'episode': 20,\n",
       "   'name': 'Three of a Kind',\n",
       "   'air_date': '1999-05-03 01:00:00'},\n",
       "  {'season': 6,\n",
       "   'episode': 21,\n",
       "   'name': 'Field Trip',\n",
       "   'air_date': '1999-05-10 01:00:00'},\n",
       "  {'season': 6,\n",
       "   'episode': 22,\n",
       "   'name': 'Biogenesis',\n",
       "   'air_date': '1999-05-17 01:00:00'},\n",
       "  {'season': 7,\n",
       "   'episode': 1,\n",
       "   'name': 'The Sixth Extinction (1)',\n",
       "   'air_date': '1999-11-08 02:00:00'},\n",
       "  {'season': 7,\n",
       "   'episode': 2,\n",
       "   'name': 'The Sixth Extinction (2): Amor Fati',\n",
       "   'air_date': '1999-11-15 02:00:00'},\n",
       "  {'season': 7,\n",
       "   'episode': 3,\n",
       "   'name': 'Hungry',\n",
       "   'air_date': '1999-11-22 02:00:00'},\n",
       "  {'season': 7,\n",
       "   'episode': 4,\n",
       "   'name': 'Millennium',\n",
       "   'air_date': '1999-11-29 02:00:00'},\n",
       "  {'season': 7,\n",
       "   'episode': 5,\n",
       "   'name': 'Rush',\n",
       "   'air_date': '1999-12-06 02:00:00'},\n",
       "  {'season': 7,\n",
       "   'episode': 6,\n",
       "   'name': 'The Goldberg Variation',\n",
       "   'air_date': '1999-12-13 02:00:00'},\n",
       "  {'season': 7,\n",
       "   'episode': 7,\n",
       "   'name': 'Orison',\n",
       "   'air_date': '2000-01-10 02:00:00'},\n",
       "  {'season': 7,\n",
       "   'episode': 8,\n",
       "   'name': 'The Amazing Maleeni',\n",
       "   'air_date': '2000-01-17 02:00:00'},\n",
       "  {'season': 7,\n",
       "   'episode': 9,\n",
       "   'name': 'Signs and Wonders',\n",
       "   'air_date': '2000-01-24 02:00:00'},\n",
       "  {'season': 7,\n",
       "   'episode': 10,\n",
       "   'name': 'Sein und Zeit (1)',\n",
       "   'air_date': '2000-02-07 02:00:00'},\n",
       "  {'season': 7,\n",
       "   'episode': 11,\n",
       "   'name': 'Closure (2)',\n",
       "   'air_date': '2000-02-14 02:00:00'},\n",
       "  {'season': 7,\n",
       "   'episode': 12,\n",
       "   'name': 'X-Cops',\n",
       "   'air_date': '2000-02-21 02:00:00'},\n",
       "  {'season': 7,\n",
       "   'episode': 13,\n",
       "   'name': 'First Person Shooter',\n",
       "   'air_date': '2000-02-28 02:00:00'},\n",
       "  {'season': 7,\n",
       "   'episode': 14,\n",
       "   'name': 'Theef',\n",
       "   'air_date': '2000-03-13 02:00:00'},\n",
       "  {'season': 7,\n",
       "   'episode': 15,\n",
       "   'name': 'En Ami',\n",
       "   'air_date': '2000-03-20 02:00:00'},\n",
       "  {'season': 7,\n",
       "   'episode': 16,\n",
       "   'name': 'Chimera',\n",
       "   'air_date': '2000-04-03 01:00:00'},\n",
       "  {'season': 7,\n",
       "   'episode': 17,\n",
       "   'name': 'all things',\n",
       "   'air_date': '2000-04-10 01:00:00'},\n",
       "  {'season': 7,\n",
       "   'episode': 18,\n",
       "   'name': 'Brand X',\n",
       "   'air_date': '2000-04-17 01:00:00'},\n",
       "  {'season': 7,\n",
       "   'episode': 19,\n",
       "   'name': 'Hollywood A.D.',\n",
       "   'air_date': '2000-05-01 01:00:00'},\n",
       "  {'season': 7,\n",
       "   'episode': 20,\n",
       "   'name': 'Fight Club',\n",
       "   'air_date': '2000-05-08 01:00:00'},\n",
       "  {'season': 7,\n",
       "   'episode': 21,\n",
       "   'name': 'Je Souhaite',\n",
       "   'air_date': '2000-05-15 01:00:00'},\n",
       "  {'season': 7,\n",
       "   'episode': 22,\n",
       "   'name': 'Requiem',\n",
       "   'air_date': '2000-05-22 01:00:00'},\n",
       "  {'season': 8,\n",
       "   'episode': 1,\n",
       "   'name': 'Within (1)',\n",
       "   'air_date': '2000-11-06 02:00:00'},\n",
       "  {'season': 8,\n",
       "   'episode': 2,\n",
       "   'name': 'Without (2)',\n",
       "   'air_date': '2000-11-13 02:00:00'},\n",
       "  {'season': 8,\n",
       "   'episode': 3,\n",
       "   'name': 'Patience',\n",
       "   'air_date': '2000-11-20 02:00:00'},\n",
       "  {'season': 8,\n",
       "   'episode': 4,\n",
       "   'name': 'Roadrunners',\n",
       "   'air_date': '2000-11-27 02:00:00'},\n",
       "  {'season': 8,\n",
       "   'episode': 5,\n",
       "   'name': 'Invocation',\n",
       "   'air_date': '2000-12-04 02:00:00'},\n",
       "  {'season': 8,\n",
       "   'episode': 6,\n",
       "   'name': 'Redrum',\n",
       "   'air_date': '2000-12-11 02:00:00'},\n",
       "  {'season': 8,\n",
       "   'episode': 7,\n",
       "   'name': 'Via Negativa',\n",
       "   'air_date': '2000-12-18 02:00:00'},\n",
       "  {'season': 8,\n",
       "   'episode': 8,\n",
       "   'name': 'Surekill',\n",
       "   'air_date': '2001-01-08 02:00:00'},\n",
       "  {'season': 8,\n",
       "   'episode': 9,\n",
       "   'name': 'Salvage',\n",
       "   'air_date': '2001-01-15 02:00:00'},\n",
       "  {'season': 8,\n",
       "   'episode': 10,\n",
       "   'name': 'Badlaa',\n",
       "   'air_date': '2001-01-22 02:00:00'},\n",
       "  {'season': 8,\n",
       "   'episode': 11,\n",
       "   'name': 'The Gift',\n",
       "   'air_date': '2001-02-05 02:00:00'},\n",
       "  {'season': 8,\n",
       "   'episode': 12,\n",
       "   'name': 'Medusa',\n",
       "   'air_date': '2001-02-12 02:00:00'},\n",
       "  {'season': 8,\n",
       "   'episode': 13,\n",
       "   'name': 'Per Manum',\n",
       "   'air_date': '2001-02-19 02:00:00'},\n",
       "  {'season': 8,\n",
       "   'episode': 14,\n",
       "   'name': 'This is Not Happening (1)',\n",
       "   'air_date': '2001-02-26 02:00:00'},\n",
       "  {'season': 8,\n",
       "   'episode': 15,\n",
       "   'name': 'DeadAlive (2)',\n",
       "   'air_date': '2001-04-02 01:00:00'},\n",
       "  {'season': 8,\n",
       "   'episode': 16,\n",
       "   'name': 'Three Words',\n",
       "   'air_date': '2001-04-09 01:00:00'},\n",
       "  {'season': 8,\n",
       "   'episode': 17,\n",
       "   'name': 'Empedocles',\n",
       "   'air_date': '2001-04-16 01:00:00'},\n",
       "  {'season': 8,\n",
       "   'episode': 18,\n",
       "   'name': 'Vienen',\n",
       "   'air_date': '2001-04-23 01:00:00'},\n",
       "  {'season': 8,\n",
       "   'episode': 19,\n",
       "   'name': 'Alone',\n",
       "   'air_date': '2001-05-07 01:00:00'},\n",
       "  {'season': 8,\n",
       "   'episode': 20,\n",
       "   'name': 'Essence (1)',\n",
       "   'air_date': '2001-05-14 01:00:00'},\n",
       "  {'season': 8,\n",
       "   'episode': 21,\n",
       "   'name': 'Existence (2)',\n",
       "   'air_date': '2001-05-21 01:00:00'},\n",
       "  {'season': 9,\n",
       "   'episode': 1,\n",
       "   'name': 'Nothing Important Happened Today (1)',\n",
       "   'air_date': '2001-11-12 02:00:00'},\n",
       "  {'season': 9,\n",
       "   'episode': 2,\n",
       "   'name': 'Nothing Important Happened Today (2)',\n",
       "   'air_date': '2001-11-19 02:00:00'},\n",
       "  {'season': 9,\n",
       "   'episode': 3,\n",
       "   'name': 'Dæmonicus',\n",
       "   'air_date': '2001-12-03 02:00:00'},\n",
       "  {'season': 9,\n",
       "   'episode': 4,\n",
       "   'name': '4-D',\n",
       "   'air_date': '2001-12-10 02:00:00'},\n",
       "  {'season': 9,\n",
       "   'episode': 5,\n",
       "   'name': 'Lord of the Flies',\n",
       "   'air_date': '2001-12-17 02:00:00'},\n",
       "  {'season': 9,\n",
       "   'episode': 6,\n",
       "   'name': 'Trust No 1',\n",
       "   'air_date': '2002-01-07 02:00:00'},\n",
       "  {'season': 9,\n",
       "   'episode': 7,\n",
       "   'name': 'John Doe',\n",
       "   'air_date': '2002-01-14 02:00:00'},\n",
       "  {'season': 9,\n",
       "   'episode': 8,\n",
       "   'name': 'Hellbound',\n",
       "   'air_date': '2002-01-28 02:00:00'},\n",
       "  {'season': 9,\n",
       "   'episode': 9,\n",
       "   'name': 'Provenance (1)',\n",
       "   'air_date': '2002-03-04 02:00:00'},\n",
       "  {'season': 9,\n",
       "   'episode': 10,\n",
       "   'name': 'Providence (2)',\n",
       "   'air_date': '2002-03-11 02:00:00'},\n",
       "  {'season': 9,\n",
       "   'episode': 11,\n",
       "   'name': 'Audrey Pauley',\n",
       "   'air_date': '2002-03-18 02:00:00'},\n",
       "  {'season': 9,\n",
       "   'episode': 12,\n",
       "   'name': 'Underneath',\n",
       "   'air_date': '2002-04-01 02:00:00'},\n",
       "  {'season': 9,\n",
       "   'episode': 13,\n",
       "   'name': 'Improbable',\n",
       "   'air_date': '2002-04-08 01:00:00'},\n",
       "  {'season': 9,\n",
       "   'episode': 14,\n",
       "   'name': 'Scary Monsters',\n",
       "   'air_date': '2002-04-15 01:00:00'},\n",
       "  {'season': 9,\n",
       "   'episode': 15,\n",
       "   'name': 'Jump the Shark',\n",
       "   'air_date': '2002-04-22 01:00:00'},\n",
       "  {'season': 9,\n",
       "   'episode': 16,\n",
       "   'name': 'William',\n",
       "   'air_date': '2002-04-29 01:00:00'},\n",
       "  {'season': 9,\n",
       "   'episode': 17,\n",
       "   'name': 'Release',\n",
       "   'air_date': '2002-05-06 01:00:00'},\n",
       "  {'season': 9,\n",
       "   'episode': 18,\n",
       "   'name': 'Sunshine Days',\n",
       "   'air_date': '2002-05-13 01:00:00'},\n",
       "  {'season': 9,\n",
       "   'episode': 19,\n",
       "   'name': 'The Truth (1)',\n",
       "   'air_date': '2002-05-20 01:00:00'},\n",
       "  {'season': 9,\n",
       "   'episode': 20,\n",
       "   'name': 'The Truth (2)',\n",
       "   'air_date': '2002-05-20 01:00:00'},\n",
       "  {'season': 10,\n",
       "   'episode': 1,\n",
       "   'name': 'My Struggle',\n",
       "   'air_date': '2016-01-25 03:00:00'},\n",
       "  {'season': 10,\n",
       "   'episode': 2,\n",
       "   'name': \"Founder's Mutation\",\n",
       "   'air_date': '2016-01-26 01:00:00'},\n",
       "  {'season': 10,\n",
       "   'episode': 3,\n",
       "   'name': 'Mulder and Scully Meet the Were-Monster',\n",
       "   'air_date': '2016-02-02 01:00:00'},\n",
       "  {'season': 10,\n",
       "   'episode': 4,\n",
       "   'name': 'Home Again',\n",
       "   'air_date': '2016-02-09 01:00:00'},\n",
       "  {'season': 10,\n",
       "   'episode': 5,\n",
       "   'name': 'Babylon',\n",
       "   'air_date': '2016-02-16 01:00:00'},\n",
       "  {'season': 10,\n",
       "   'episode': 6,\n",
       "   'name': 'My Struggle II',\n",
       "   'air_date': '2016-02-23 01:00:00'},\n",
       "  {'season': 11,\n",
       "   'episode': 1,\n",
       "   'name': 'My Struggle III',\n",
       "   'air_date': '2018-01-04 01:00:00'},\n",
       "  {'season': 11,\n",
       "   'episode': 2,\n",
       "   'name': 'This',\n",
       "   'air_date': '2018-01-11 01:00:00'},\n",
       "  {'season': 11,\n",
       "   'episode': 3,\n",
       "   'name': 'Plus One',\n",
       "   'air_date': '2018-01-18 01:00:00'},\n",
       "  {'season': 11,\n",
       "   'episode': 4,\n",
       "   'name': 'The Lost Art of Forehead Sweat',\n",
       "   'air_date': '2018-01-25 01:00:00'},\n",
       "  {'season': 11,\n",
       "   'episode': 5,\n",
       "   'name': 'Ghouli',\n",
       "   'air_date': '2018-02-01 01:00:00'},\n",
       "  {'season': 11,\n",
       "   'episode': 6,\n",
       "   'name': 'Kitten',\n",
       "   'air_date': '2018-02-08 01:00:00'},\n",
       "  {'season': 11,\n",
       "   'episode': 7,\n",
       "   'name': 'Rm9sbG93ZXJz',\n",
       "   'air_date': '2018-03-01 01:00:00'},\n",
       "  {'season': 11,\n",
       "   'episode': 8,\n",
       "   'name': 'Familiar',\n",
       "   'air_date': '2018-03-08 01:00:00'},\n",
       "  {'season': 11,\n",
       "   'episode': 9,\n",
       "   'name': 'Nothing Lasts Forever',\n",
       "   'air_date': '2018-03-15 00:00:00'},\n",
       "  {'season': 11,\n",
       "   'episode': 10,\n",
       "   'name': 'My Struggle IV',\n",
       "   'air_date': '2018-03-22 00:00:00'}]}"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tv_shows_json.loc[0, \"shows\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 12.4.1 Problems"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 12.4.2 Solutions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>season</th>\n",
       "      <th>episode</th>\n",
       "      <th>name</th>\n",
       "      <th>air_date</th>\n",
       "      <th>show</th>\n",
       "      <th>runtime</th>\n",
       "      <th>network</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Pilot</td>\n",
       "      <td>1993-09-11 01:00:00</td>\n",
       "      <td>The X-Files</td>\n",
       "      <td>60</td>\n",
       "      <td>FOX</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>Deep Throat</td>\n",
       "      <td>1993-09-18 01:00:00</td>\n",
       "      <td>The X-Files</td>\n",
       "      <td>60</td>\n",
       "      <td>FOX</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>Squeeze</td>\n",
       "      <td>1993-09-25 01:00:00</td>\n",
       "      <td>The X-Files</td>\n",
       "      <td>60</td>\n",
       "      <td>FOX</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>Conduit</td>\n",
       "      <td>1993-10-02 01:00:00</td>\n",
       "      <td>The X-Files</td>\n",
       "      <td>60</td>\n",
       "      <td>FOX</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>The Jersey Devil</td>\n",
       "      <td>1993-10-09 01:00:00</td>\n",
       "      <td>The X-Files</td>\n",
       "      <td>60</td>\n",
       "      <td>FOX</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>477</th>\n",
       "      <td>7</td>\n",
       "      <td>18</td>\n",
       "      <td>Dirty Girls</td>\n",
       "      <td>2003-04-16 00:00:00</td>\n",
       "      <td>Buffy the Vampire Slayer</td>\n",
       "      <td>60</td>\n",
       "      <td>UPN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>478</th>\n",
       "      <td>7</td>\n",
       "      <td>19</td>\n",
       "      <td>Empty Places</td>\n",
       "      <td>2003-04-30 00:00:00</td>\n",
       "      <td>Buffy the Vampire Slayer</td>\n",
       "      <td>60</td>\n",
       "      <td>UPN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>479</th>\n",
       "      <td>7</td>\n",
       "      <td>20</td>\n",
       "      <td>Touched</td>\n",
       "      <td>2003-05-07 00:00:00</td>\n",
       "      <td>Buffy the Vampire Slayer</td>\n",
       "      <td>60</td>\n",
       "      <td>UPN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>480</th>\n",
       "      <td>7</td>\n",
       "      <td>21</td>\n",
       "      <td>End of Days</td>\n",
       "      <td>2003-05-14 00:00:00</td>\n",
       "      <td>Buffy the Vampire Slayer</td>\n",
       "      <td>60</td>\n",
       "      <td>UPN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>481</th>\n",
       "      <td>7</td>\n",
       "      <td>22</td>\n",
       "      <td>Chosen</td>\n",
       "      <td>2003-05-21 00:00:00</td>\n",
       "      <td>Buffy the Vampire Slayer</td>\n",
       "      <td>60</td>\n",
       "      <td>UPN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>482 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     season  episode              name             air_date  \\\n",
       "0         1        1             Pilot  1993-09-11 01:00:00   \n",
       "1         1        2       Deep Throat  1993-09-18 01:00:00   \n",
       "2         1        3           Squeeze  1993-09-25 01:00:00   \n",
       "3         1        4           Conduit  1993-10-02 01:00:00   \n",
       "4         1        5  The Jersey Devil  1993-10-09 01:00:00   \n",
       "..      ...      ...               ...                  ...   \n",
       "477       7       18       Dirty Girls  2003-04-16 00:00:00   \n",
       "478       7       19      Empty Places  2003-04-30 00:00:00   \n",
       "479       7       20           Touched  2003-05-07 00:00:00   \n",
       "480       7       21       End of Days  2003-05-14 00:00:00   \n",
       "481       7       22            Chosen  2003-05-21 00:00:00   \n",
       "\n",
       "                         show runtime network  \n",
       "0                 The X-Files      60     FOX  \n",
       "1                 The X-Files      60     FOX  \n",
       "2                 The X-Files      60     FOX  \n",
       "3                 The X-Files      60     FOX  \n",
       "4                 The X-Files      60     FOX  \n",
       "..                        ...     ...     ...  \n",
       "477  Buffy the Vampire Slayer      60     UPN  \n",
       "478  Buffy the Vampire Slayer      60     UPN  \n",
       "479  Buffy the Vampire Slayer      60     UPN  \n",
       "480  Buffy the Vampire Slayer      60     UPN  \n",
       "481  Buffy the Vampire Slayer      60     UPN  \n",
       "\n",
       "[482 rows x 7 columns]"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tv_shows = pd.json_normalize(\n",
    "    data = tv_shows_json[\"shows\"],\n",
    "    record_path = \"episodes\",\n",
    "    meta = [\"show\", \"runtime\", \"network\"]\n",
    ")\n",
    "\n",
    "tv_shows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "xfiles = tv_shows[tv_shows[\"show\"] == \"The X-Files\"]\n",
    "lost = tv_shows[tv_shows[\"show\"] == \"Lost\"]\n",
    "buffy = tv_shows[tv_shows[\"show\"] == \"Buffy the Vampire Slayer\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pandas.io.excel._openpyxl.OpenpyxlWriter at 0x7fc013972760>"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "episodes = pd.ExcelWriter(\"episodes.xlsx\")\n",
    "episodes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "xfiles.to_excel(\n",
    "    excel_writer = episodes, sheet_name = \"X-Files\", index = False\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "lost.to_excel(\n",
    "    excel_writer = episodes, sheet_name = \"Lost\", index = False\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "buffy.to_excel(\n",
    "    excel_writer = episodes,\n",
    "    sheet_name = \"Buffy the Vampire Slayer\",\n",
    "    index = False\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "episodes.save()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 12.5 Summary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
